package com.blockchain.front.service.financial.achieve;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.codec.digest.UnixCrypt;

import com.blockchain.common.enums.ChargeStatus;
import com.blockchain.common.enums.ChargeType;
import com.blockchain.common.enums.EntrustType;
import com.blockchain.common.enums.IsPass;
import com.blockchain.common.enums.JyztStatus;
import com.blockchain.common.enums.WithdrawStatus;
import com.blockchain.framework.config.ConfigureProvider;
import com.blockchain.framework.resource.ResourceNotFoundException;
import com.blockchain.framework.resource.ResourceRegister;
import com.blockchain.framework.service.ServiceFactory;
import com.blockchain.framework.service.ServiceResource;
import com.blockchain.framework.service.exception.LogicalException;
import com.blockchain.framework.service.exception.ParameterException;
import com.blockchain.framework.service.query.ArrayParser;
import com.blockchain.framework.service.query.ItemParser;
import com.blockchain.framework.service.query.Paging;
import com.blockchain.framework.service.query.PagingResult;
import com.blockchain.front.config.FrontConst;
import com.blockchain.front.service.financial.FinancialManage;
import com.blockchain.front.service.financial.entity.JlEntity;
import com.blockchain.front.service.financial.entity.RechargeQrEntity;
import com.blockchain.front.service.financial.entity.TxRecordEntity;
import com.blockchain.front.service.financial.entity.UserZhEntity;
import com.blockchain.front.service.financial.entity.XlbEntity;
import com.blockchain.front.util.MD5;
import com.blockchain.util.Formater;
import com.blockchain.util.StringHelper;
import com.blockchain.util.parser.EnumParser;
import com.blockchain.variables.P2PConst;
import com.blockchain.variables.defines.SystemVariable;
import com.cuohe.models.Market;

public class FinanciaManageImpl extends AbstractFinanciaService implements FinancialManage {

	public static class FinancialManageFactory implements ServiceFactory<FinancialManage> {

		@Override
		public FinancialManage newInstance(ServiceResource serviceResource) {
			return new FinanciaManageImpl(serviceResource);
		}
	}

	public FinanciaManageImpl(ServiceResource serviceResource) {
		super(serviceResource);
	}

	@Override
	public UserZhEntity getCny() throws Throwable {
		return select(getConnection(P2PConst.DB_USER), new ItemParser<UserZhEntity>() {

			@Override
			public UserZhEntity parse(ResultSet re) throws SQLException {
				UserZhEntity z = new UserZhEntity();
				while (re.next()) {
					z.cny_ze = re.getBigDecimal(1);
					z.cny_dj = re.getBigDecimal(2);
					z.cny_ky = re.getBigDecimal(3);
				}
				return z;
			}
		}, "SELECT F02,F03,F04 FROM T6023 WHERE F01=?", serviceResource.getSession().getAccountId());
	}

	@Override
	public UserZhEntity[] getZhXlb() throws Throwable {
		StringBuffer sql = new StringBuffer("SELECT T6025.F03 bid,T6025.F04 ky,T6025.F05 dj,");
		sql.append(" T6013.F03 ywm,T6013.F04 zwm,T6013.F05 tblj,");
		sql.append(" T6015.F01 scid,T6016.F04 zxj,T6015.F05 ws,T6025.F06 sd");
		sql.append(" FROM T6025 LEFT JOIN T6013 ON T6025.F03=T6013.F01");
		sql.append(" LEFT JOIN T6015 ON T6015.F04=T6025.F03");
		sql.append(" LEFT JOIN T6016 ON T6016.F02=T6015.F01");
		sql.append(" WHERE T6025.F02=?  ORDER BY T6025.F04 DESC");
		return selectAll(getConnection(P2PConst.DB_USER), new ArrayParser<UserZhEntity>() {
			ArrayList<UserZhEntity> list = new ArrayList<UserZhEntity>();

			@Override
			public UserZhEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					UserZhEntity x = new UserZhEntity();
					x.bid = re.getInt(1);
					x.xlb_ky = re.getBigDecimal(2);
					x.xlb_dj = re.getBigDecimal(3);
					x.ywjc = re.getString(4);
					x.zwm = re.getString(5);
					x.tblj = re.getString(6);
					x.scid = re.getInt(7);
					x.zxj = re.getBigDecimal(8);
					x.ws = re.getInt(9);
					x.xlb_sd = re.getBigDecimal(10);
					if (list == null) {
						list = new ArrayList<>();
					}
					list.add(x);
				}
				return list == null ? null : list.toArray(new UserZhEntity[list.size()]);
			}
		}, sql.toString(), serviceResource.getSession().getAccountId());
	}

	@Override
	public PagingResult<JlEntity> searchCjjl(int scid, JyztStatus s, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"SELECT T6018.F11 sj,T6018.F10 zt,T6018.F05 jg,T6018.F06 sl, T6018.F07 zje,T6018.F08 mr_sxf,T6018.F09 mc_sxf,");
		sql.append("  mr.F03 ywm_c,mr.F04 zwm_c, mc.F03 ywm_r,mc.F04 zwm_r, T6015.F05 ws,T6018.F03 mr_id,T6018.F04 mc_id ");
		sql.append("  FROM T6018 LEFT JOIN T6015 ON T6018.F02=T6015.F01");
		sql.append("  LEFT JOIN T6013 mr ON mr.F01=T6015.F03 ");
		sql.append("  LEFT JOIN T6013 mc ON mc.F01=T6015.F04 ");
		sql.append(" WHERE 1=1");
		sql.append(" AND T6015.F01=? AND (T6018.F03=? OR T6018.F04=?)");
		if (scid <= 0) {
			scid = getScid();
		}
		final int userid = serviceResource.getSession().getAccountId();
		parameters.add(scid);
		parameters.add(userid);
		parameters.add(userid);
		if (s != null) {
			if (s == JyztStatus.MRMC) {
				sql.append(" AND T6018.F03=T6018.F04");
			} else if (s == JyztStatus.MR) {
				sql.append(" AND T6018.F03=? ");
				sql.append(" AND T6018.F03<>T6018.F04");
				parameters.add(userid);
			} else {
				sql.append(" AND T6018.F04=? ");
				sql.append(" AND T6018.F03<>T6018.F04");
				parameters.add(userid);
			}

		}

		sql.append(" ORDER BY T6018.F11 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<JlEntity>() {
			ArrayList<JlEntity> list = new ArrayList<JlEntity>();

			@Override
			public JlEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					JlEntity x = new JlEntity();
					x.sj = re.getTimestamp(1);
					x.jyztStatus = EnumParser.parse(JyztStatus.class, re.getString(2));
					x.cjjg = re.getBigDecimal(3);
					x.cjsl = re.getBigDecimal(4);
					x.ze = re.getBigDecimal(5);
					x.mr_sxf = re.getBigDecimal(6);
					x.mc_sxf = re.getBigDecimal(7);
					x.ywm_m = re.getString(8);
					x.zwm_m = re.getString(9);
					x.ywm_s = re.getString(10);
					x.zwm_s = re.getString(11);
					x.ws = re.getInt(12);
					x.mr_id = re.getInt(13);
					x.mc_id = re.getInt(14);
					if (x.mr_id == userid && x.mc_id != userid) {
						x.zt = "买";
						x.sxf = Formater.formatAmount_(x.mr_sxf, x.ws) + x.ywm_s;
						x.ys="buy";
					} else if (x.mc_id == userid && x.mr_id != userid) {
						x.zt = "卖";
						x.sxf = Formater.formatAmount_(x.mc_sxf, x.ws) + x.ywm_m;
						x.ys="sell";
					} else {
						x.zt = "买/卖";
						x.sxf = Formater.formatAmount_(x.mr_sxf, x.ws) + x.ywm_s + "/"
								+ Formater.formatAmount_(x.mc_sxf, x.ws) + x.ywm_m;
					}
					list.add(x);
				}
				return list == null ? null : list.toArray(new JlEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	public int getbid() throws SQLException {
		return selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6013  WHERE T6013.F01>0 ORDER BY T6013.F01 ASC LIMIT 1");
	}

	public int getzrbid() throws SQLException {
		return selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6013  WHERE T6013.F06='S' AND T6013.F01>0 ORDER BY T6013.F01 ASC LIMIT 1");
	}
	

	public int getzcbid() throws SQLException {
		return selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6013  WHERE T6013.F07='S' AND T6013.F01>0 ORDER BY T6013.F01 ASC LIMIT 1");
	}
	
	private int getScid() throws SQLException {
		return selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6015  ORDER BY T6015.F01 ASC LIMIT 1");
	}

	@Override
	public PagingResult<JlEntity> searchWtjl(int scid, JyztStatus t, EntrustType s, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT T6017.F09 sj,T6017.F07 zt,T6017.F04 jg,T6017.F05 sl,");
		sql.append(" 	T6017.F06 ycjsl,T6017.F11 cjje,T6017.F08 type,T6017.F10 sfch,T6015.F05 ws,T6017.F01 id,");
		sql.append(" 	mr.F03 ywm_c,mr.F04 zwm_c, mc.F03 ywm_r,mc.F04 zwm_r,T6017.F02 scid");
		sql.append("  	FROM T6017 LEFT JOIN T6015 ON T6017.F02=T6015.F01");
		sql.append("  	LEFT JOIN T6013 mr ON mr.F01=T6015.F03 ");
		sql.append("  	LEFT JOIN T6013 mc ON mc.F01=T6015.F04 ");
		sql.append("  	WHERE 1=1");
		sql.append("  	AND T6015.F01=? AND T6017.F03=? ");
		if (scid <= 0) {
			scid = getScid();
		}
		parameters.add(scid);
		parameters.add(serviceResource.getSession().getAccountId());
		if (t != null) {
			sql.append(" AND T6017.F07=?");
			parameters.add(t);
		}
		if (s != null) {
			sql.append(" AND T6017.F08=?");
			parameters.add(s);
		}

		sql.append(" ORDER BY T6017.F09 DESC");

		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<JlEntity>() {
			ArrayList<JlEntity> list = new ArrayList<JlEntity>();

			@Override
			public JlEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					JlEntity x = new JlEntity();
					x.sj = re.getTimestamp(1);
					x.jyztStatus = EnumParser.parse(JyztStatus.class, re.getString(2));
					x.cjjg = re.getBigDecimal(3);
					x.cjsl = re.getBigDecimal(4);
					x.ycjsl = re.getBigDecimal(5);
					x.ze = re.getBigDecimal(6);
					x.entrustType = EnumParser.parse(EntrustType.class, re.getString(7));
					x.isPass = EnumParser.parse(IsPass.class, re.getString(8));
					x.ws = re.getInt(9);
					x.wtid = re.getLong(10);
					x.ywm_m = re.getString(11);
					x.zwm_m = re.getString(12);
					x.ywm_s = re.getString(13);
					x.zwm_s = re.getString(14);
					x.scid = re.getLong(15);
					list.add(x);
				}
				return list == null ? null : list.toArray(new JlEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	@SuppressWarnings("static-access")
	@Override
	public void cxWt(int id, int... parameter) throws Throwable {
		serviceResource.openTransactions();
		try {
			int userid = 0;
			if (parameter.length >= 2) {
				userid = parameter[1];
			} else {
				userid = serviceResource.getSession().getAccountId();
			}
			if (userid <= 0) {
				throw new ParameterException("err_1021");
			}
			if (id <= 0) {
				throw new ParameterException("err_1022");
			}
			if (parameter[0] <= 0) {
				throw new ParameterException("err_1023");
			}
			selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6015 WHERE F01=? ", parameter[0]);// 锁市场表
//			selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6015 WHERE F01=? FOR UPDATE ", parameter[0]);// 锁市场表
			EntrustType entrustType = null;// 委托状态
			JyztStatus jyztStatus = null;// 交易类型
			BigDecimal wtje = new BigDecimal(0);// 委托价格
			BigDecimal wtsl = new BigDecimal(0);// 委托数量
			BigDecimal ycjl = new BigDecimal(0);// 已成交数量
			BigDecimal cjje = new BigDecimal(0);// 成交金额
			try (Connection connection = getConnection(P2PConst.DB_USER)) {
				try (PreparedStatement ps = connection
						.prepareStatement("SELECT F04,F05,F06,F07,F08,F11 FROM T6017 WHERE F01=? FOR UPDATE")) {
					ps.setInt(1, id);
					try (ResultSet re = ps.executeQuery()) {
						while (re.next()) {
							wtje = re.getBigDecimal(1);
							wtsl = re.getBigDecimal(2);
							ycjl = re.getBigDecimal(3);
							jyztStatus = EnumParser.parse(JyztStatus.class, re.getString(4));
							entrustType = EnumParser.parse(EntrustType.class, re.getString(5));
							cjje = re.getBigDecimal(6);
						}
					}
				}
			}
			if (entrustType == null || entrustType != EntrustType.JYZ) {
				throw new ParameterException("err_1024");
			}
			if (jyztStatus == null) {
				throw new ParameterException("err_1022");
			}
			
			Market market = getMarkeyById(parameter[0]);
			
			if (jyztStatus.MR == jyztStatus) {
				
				//买方为人民币
				if(market.getBuyCoinID()==0) {
					selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6023 WHERE F01=? FOR UPDATE", userid);// 锁用户表
				}else {
					selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6025 WHERE F02=? FOR UPDATE", userid);// 锁用户表
				}
				BigDecimal fhje = (wtje.multiply(wtsl).subtract(cjje)).setScale(8, BigDecimal.ROUND_HALF_UP);// 返还金额
				if (fhje.compareTo(new BigDecimal(0)) > 0) {					
								
					//买方为人民币(这里暂时写死人民币的ID为16了，以后想到好的方式再优化此处)
					if(market.getBuyCoinID()==0) {
						execute(getConnection(P2PConst.DB_USER), "UPDATE T6023 SET F03=F03-?,F04=F04+? WHERE F01=?", fhje,fhje, userid);
					}else {//币币交易
						execute(getConnection(P2PConst.DB_USER), "UPDATE T6025 SET F04=F04+?,F05=F05-? WHERE F02=? AND F03=?", fhje, fhje,userid,market.getBuyCoinID());
					}
				}
			}
			if (jyztStatus.MC == jyztStatus) {
				BigDecimal fhsl = wtsl.subtract(ycjl);// 返还数量
				int bid = selectInt(P2PConst.DB_USER, "SELECT F04 FROM T6015 WHERE F01=?", parameter[0]);// 获得币ID；
				int T6025_F01=selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6025 WHERE F02=? AND F03= ?", userid,bid);
				if(T6025_F01<=0){
					throw new ParameterException("err_1025");
				}
				selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6025 WHERE F01=? FOR UPDATE", T6025_F01);//锁表
				
				if (bid > 0) {
					execute(getConnection(P2PConst.DB_USER),
							"UPDATE T6025 SET F04=F04+?,F05=F05-? WHERE F02=? AND F03=?", fhsl, fhsl, userid, bid);
				}else {
					throw new ParameterException("coin not found");
				}
			}
			execute(getConnection(P2PConst.DB_USER), "UPDATE T6017 SET F08=? WHERE F01=?", EntrustType.YCX, id);
		} catch (Exception e) {
			serviceResource.rollback();
			e.printStackTrace();
			throw new ParameterException(e.getMessage());
		}
	}
	
	/*
	 * 通过市场ID，获取记录
	 * */
	private Market getMarkeyById(int markId) throws ResourceNotFoundException, SQLException {
		String sql  = "SELECT * FROM "+P2PConst.DB_USER+".T6015 WHERE F01=?";
		return this.select(getConnection(P2PConst.DB_USER), new ItemParser<Market>() {
			Market m = new Market();
			@Override
			public Market parse(ResultSet rs) throws SQLException {
				while (rs.next()) {
					m.setMarkID(rs.getInt(1));
					m.setPartitionID(rs.getInt(2));
					m.setBuyCoinID(rs.getInt(3));
					m.setSellCoinID(rs.getInt(4));
					m.setDecimalDigit(rs.getInt(5));
					m.setBuyFee(rs.getDouble(6));
					m.setSellFee(rs.getDouble(7));
					m.setBuyMinPrice(rs.getDouble(8));
					m.setBuyMaxPrice(rs.getDouble(9));
					m.setSellMinPrice(rs.getDouble(10));
					m.setSellMaxPrice(rs.getDouble(11));
					m.setOpen(null != rs.getString(12) ? rs.getString(12).equalsIgnoreCase("S") : false);
					m.setShow(null != rs.getString(13) ? rs.getString(13).equalsIgnoreCase("S") : false);
					m.setTime(rs.getTimestamp(14));					
				}
				return m;
			}
		}, sql, markId);
	} 

	@Override
	public PagingResult<XlbEntity> searchXlbZc(int bid, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT T6028.F07 sj,T6028.F05 sjdz,T6028.F06 sxf,T6012.F05 qbdz,");
		sql.append(" 	T6013.F03 ywm,T6013.F04 zwm,T6028.F08 zt");
		sql.append("  	FROM T6028 LEFT JOIN T6013 ON T6028.F03=T6013.F01");
		sql.append("  	LEFT JOIN T6012 ON T6028.F04=T6012.F01");
		sql.append("  	WHERE 1=1");
		sql.append("  	AND T6028.F03=? AND T6028.F02=? ");
		if (bid <= 0) {
			bid = getbid();
		}
		parameters.add(bid);
		parameters.add(serviceResource.getSession().getAccountId());
		sql.append(" ORDER BY T6028.F07 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<XlbEntity>() {
			ArrayList<XlbEntity> list = new ArrayList<XlbEntity>();

			@Override
			public XlbEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					XlbEntity x = new XlbEntity();
					x.sj = re.getTimestamp(1);
					x.zcsl = re.getBigDecimal(2);
					x.sxf = re.getBigDecimal(3);
					x.jsdz = re.getString(4);
					x.ywm = re.getString(5);
					x.zwm = re.getString(6);
					x.isPass = EnumParser.parse(IsPass.class, re.getString(7));
					list.add(x);
				}
				return list == null ? null : list.toArray(new XlbEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	@Override
	public void addXlbZc(int bid, BigDecimal sl, String jymm, int qbid) throws Throwable {
		serviceResource.openTransactions();
		try {
			if (bid <= 0) {
				throw new ParameterException("err_1025");
			}
			if (sl == null || sl.compareTo(new BigDecimal(0)) <= 0) {
				throw new ParameterException("err_1026");
			}
			if (qbid <= 0) {
				throw new ParameterException("err_1027");
			}
			if (StringHelper.isEmpty(jymm)) {
				throw new ParameterException("err_1028");
			}
			if (!Jymm(jymm)) {
				throw new ParameterException("err_1029");
			}
			if (!sfsd()) {
				throw new ParameterException("err_1030");
			}
			if (!gjsmrz()) {
				throw new ParameterException("err_1031");
			}
			selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6025 WHERE F02=? FOR UPDATE",
					serviceResource.getSession().getAccountId());
			IsPass is = null;// 是否正常转出
			BigDecimal min_xz = new BigDecimal(0);// 转出最小限制
			BigDecimal max_xz = new BigDecimal(0);// 转出最大限制
			BigDecimal min_sxf = new BigDecimal(0);// 最低转出手续费
			BigDecimal max_sxfl = new BigDecimal(0);// 转出最高手续费率
			String mc = "";
			try (Connection connection = getConnection(P2PConst.DB_USER)) {
				try (PreparedStatement ps = connection
						.prepareStatement("SELECT F07,F08,F09,F10,F11,F03 FROM T6013 WHERE F01=? ")) {
					ps.setInt(1, bid);
					try (ResultSet re = ps.executeQuery()) {
						while (re.next()) {
							is = EnumParser.parse(IsPass.class, re.getString(1));
							min_xz = re.getBigDecimal(2);
							max_xz = re.getBigDecimal(3);
							min_sxf = re.getBigDecimal(4);
							max_sxfl = re.getBigDecimal(5);
							mc = re.getString(6);
						}
					}
				}
			}
			if (is == null || is == IsPass.F) {
				throw new ParameterException("err_1032");
			}
			if (sl.compareTo(min_xz) < 0) {
				throw new ParameterException("err_1033");
			}
			if(getZbDbzg()>0){
				max_xz=new BigDecimal(getZbDbzg());
			}
			if (sl.compareTo(max_xz) > 0) {
				throw new ParameterException("err_1034");
			}
			BigDecimal sxf = sl.multiply(max_sxfl).setScale(8, BigDecimal.ROUND_HALF_UP);
			if (sxf.compareTo(min_sxf) < 0) {
				sxf = min_sxf;
			}
			BigDecimal ky = selectBigDecimal(P2PConst.DB_USER, "SELECT F04 FROM T6025 WHERE F02=? AND F03=?",
					serviceResource.getSession().getAccountId(), bid);// 可用数量
			if ((sl).compareTo(ky) > 0) {
				throw new ParameterException("err_1035");
			}
			String bz = "数量：" + sl + mc + "手续费：" + sxf;
			execute(getConnection(P2PConst.DB_USER), "UPDATE T6025 SET F04=F04-?,F05=F05+? WHERE F02=? AND F03=?", sl,sl,
					serviceResource.getSession().getAccountId(), bid);
			execute(getConnection(P2PConst.DB_USER),
					"INSERT INTO T6028 SET F02=?,F03=?,F04=?,F05=?,F06=?,F07=CURRENT_TIMESTAMP(),F09=?",
					serviceResource.getSession().getAccountId(), bid, qbid, sl.subtract(sxf), sxf, bz);
		} catch (Exception e) {
			serviceResource.rollback();
			e.printStackTrace();
			throw new ParameterException(e.getMessage());
		}
	}

	private boolean Jymm(String jymm) throws Throwable {
		String _ymm = selectString(P2PConst.DB_USER, "SELECT F11 FROM T6011 WHERE F01=?",
				serviceResource.getSession().getAccountId());
		if (StringHelper.isEmpty(jymm)) {
			throw new LogicalException("err_1036");
		}
		String md5password = MD5.getMd5Value(jymm);
		if (md5password.equals(_ymm)) {
			execute(getConnection(FrontConst.DB_USER), "UPDATE T6011 SET F11=? WHERE F01=?",
					UnixCrypt.crypt(jymm, DigestUtils.sha256Hex(jymm)), serviceResource.getSession().getAccountId());
		} else {
			if (!UnixCrypt.crypt(jymm, DigestUtils.sha256Hex(jymm)).equals(_ymm)) {
				return false;
			}
		}
		return true;
	}

	@Override
	public XlbEntity getXlbXx(int bid) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT F07,F08,F09,F10,F11,F03 FROM T6013 WHERE F01=?");
		if (bid <= 0) {
			bid = getbid();
		}
		parameters.add(bid);
		return select(getConnection(P2PConst.DB_USER), new ItemParser<XlbEntity>() {
			@Override
			public XlbEntity parse(ResultSet re) throws SQLException {
				XlbEntity x = new XlbEntity();
				while (re.next()) {
					x.is = EnumParser.parse(IsPass.class, re.getString(1));
					x.min_xz = re.getBigDecimal(2);
					x.max_xz = re.getBigDecimal(3);
					x.min_sxf = re.getBigDecimal(4);
					x.max_sxfl = re.getBigDecimal(5);
					x.ywm=re.getString(6);
				try {
					if(getZbDbzg()>0){
						x.max_xz=new BigDecimal(getZbDbzg());	
					}
				} catch (Throwable e) {
					e.printStackTrace();
				}	
				}
				return x;
			}
		}, sql.toString(), parameters);
	}

	@Override
	public BigDecimal getKyXlb(int bid) throws Throwable {
		if (bid <= 0) {
			bid = getbid();
		}
		return selectBigDecimal(P2PConst.DB_USER, "SELECT F04 FROM T6025 WHERE F02=? AND F03=?",
				serviceResource.getSession().getAccountId(), bid);
	}

	@Override
	public PagingResult<TxRecordEntity> searchTxjl(int dzid, WithdrawStatus zt, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder(
				"SELECT T6034.F01,T6034.F03,T6034.F04,T6024.F06 AS F05,T6034.F06,T6034.F07,T6034.F09,T6034.F14 AS rate,T6034.F15 as cny");
		sql.append(" FROM T6034 LEFT JOIN T6024 ON T6034.F05=T6024.F01 WHERE T6034.F02=?");
		parameters.add(serviceResource.getSession().getAccountId());
		if (dzid > 0) {
			sql.append(" AND T6024.F01=?");
			parameters.add(dzid);
		}
		if (zt != null) {
			sql.append(" AND T6034.F07=?");
			parameters.add(zt);
		}
		sql.append(" ORDER BY T6034.F01 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<TxRecordEntity>() {
			ArrayList<TxRecordEntity> list = new ArrayList<TxRecordEntity>();

			@Override
			public TxRecordEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					TxRecordEntity x = new TxRecordEntity();
					x.id = re.getInt(1);
					x.sqsj = re.getTimestamp(2);
					x.txje = re.getBigDecimal(3);
					x.yhkh = re.getString(4);
					x.fy = re.getBigDecimal(5);
					x.type = EnumParser.parse(WithdrawStatus.class, re.getString(6));
					x.remark = re.getString(7);
					x.rate = re.getBigDecimal(8);
					x.cny = re.getBigDecimal(9);
					list.add(x);
				}
				return list == null ? null : list.toArray(new TxRecordEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	@Override
	public void withdraw(BigDecimal txje, String txmm, int dzid,BigDecimal rate) throws Throwable {
		serviceResource.openTransactions();
		try {
			int accountId = serviceResource.getSession().getAccountId();
			ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
			if (txje == null || txje.compareTo(new BigDecimal(0)) <= 0) {
				throw new ParameterException("err_1037");
			}
			if (dzid <= 0) {
				throw new ParameterException("err_1038");
			}
			if (StringHelper.isEmpty(txmm)) {
				throw new ParameterException("err_1028");
			}
			if (!Jymm(txmm)) {
				throw new ParameterException("err_1029");
			}
			if (!sfsd()) {
				throw new ParameterException("err_1030");
			}
			if (!gjsmrz()) {
				throw new ParameterException("err_1031");
			}
			BigDecimal fv = new BigDecimal(configureProvider.getProperty(SystemVariable.WITHDRAW_FV));
			BigDecimal min = new BigDecimal(configureProvider.getProperty(SystemVariable.WITHDRAW_MIN_FUNDS));
			BigDecimal max = new BigDecimal(configureProvider.getProperty(SystemVariable.WITHDRAW_MAX_FUNDS));
			BigDecimal mrxe = new BigDecimal(configureProvider.getProperty(SystemVariable.WIDTHDRAW_PER_LIMIT));
			BigDecimal min_sxf = new BigDecimal(configureProvider.getProperty(SystemVariable.WITHDRAW_MIN_SXF));
			if (txje.compareTo(min) < 0) {
				throw new ParameterException("err_1039");
			}
			if (txje.compareTo(max) > 0) {
				throw new ParameterException("err_1040");
			}
			BigDecimal amountTotal = txjeInputTotal();
			// 判断每日提现金额
			if (amountTotal.add(txje).compareTo(mrxe) > 0) {
				throw new LogicalException("每日累计提现金额不能超过"
						+ configureProvider.getProperty(SystemVariable.WIDTHDRAW_PER_LIMIT) + "，请确认后提现！");
			}

			// 修改每日提现金额
			txjeUpdateTotal(txje);
			
			// 提现手续费
			BigDecimal sxf = txje.multiply(fv).setScale(8, BigDecimal.ROUND_HALF_UP);
			
			if (sxf.compareTo(txje) > 0) {
				throw new LogicalException("err_1041");
			}
			if (sxf.compareTo(min_sxf) < 0) {
				sxf = min_sxf;
			}

			BigDecimal zhye = selectBigDecimal(P2PConst.DB_USER, "SELECT F04 FROM T6023 WHERE F01=? FOR UPDATE",
					serviceResource.getSession().getAccountId());
			if (txje.compareTo(zhye) > 0) {
				throw new LogicalException("err_1042");
			}
			BigDecimal sjdz = txje.subtract(sxf);

			BigDecimal cny = sjdz.multiply(rate).setScale(2,RoundingMode.DOWN);
			
			execute(getConnection(P2PConst.DB_USER),
					"INSERT INTO T6034 SET F02=?,F03=CURRENT_TIMESTAMP(),F04=?,F05=?,F06=?,F07=?,F14=?,F15=?", accountId, sjdz,
					dzid, sxf, WithdrawStatus.WSH,rate,cny);
			execute(getConnection(P2PConst.DB_USER), "UPDATE T6023 SET F03=F03+?,F04=F04-? WHERE F01=?", txje, txje,
					serviceResource.getSession().getAccountId());
		} catch (Throwable e) {
			serviceResource.rollback();
			e.printStackTrace();
			throw new LogicalException(e.getMessage());
		}

	}

	protected BigDecimal txjeInputTotal() throws Throwable {
		try (Connection connection = getConnection(P2PConst.DB_USER)) {
			try (PreparedStatement ps = connection.prepareStatement("SELECT F10 FROM T6010 WHERE F01=?")) {
				ps.setInt(1, serviceResource.getSession().getAccountId());
				try (ResultSet resultSet = ps.executeQuery()) {
					if (resultSet.next()) {
						return resultSet.getBigDecimal(1);
					}
				}
			}
		}
		return new BigDecimal("0");
	}

	protected void txjeUpdateTotal(BigDecimal amount) throws Throwable {
		try (Connection connection = getConnection(P2PConst.DB_USER)) {
			try (PreparedStatement ps = connection.prepareStatement("UPDATE T6010 SET F10=F10+? WHERE F01=?")) {
				ps.setBigDecimal(1, amount);
				ps.setInt(2, serviceResource.getSession().getAccountId());
				ps.executeUpdate();
			}
		}
	}

	@Override
	public PagingResult<TxRecordEntity> searchCzjl(ChargeType type, ChargeStatus zt, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("SELECT T6033.F01,T6033.F03,T6033.F04,T6033.F05,T6033.F11,T6033.F13");
		sql.append(", T6033_1.F02 AS rate, T6033_1.F03 AS cny");
		sql.append(" FROM T6033 LEFT JOIN t6033_1 ON (T6033.F01 = T6033_1.F01) WHERE T6033.F02=?");
		parameters.add(serviceResource.getSession().getAccountId());
		if (type != null) {
			sql.append(" AND T6033.F11=?");
			parameters.add(type);
		}
		if (zt != null) {
			sql.append(" AND T6033.F05=?");
			parameters.add(zt);
		}
		sql.append(" ORDER BY T6033.F01 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<TxRecordEntity>() {
			ArrayList<TxRecordEntity> list = new ArrayList<TxRecordEntity>();

			@Override
			public TxRecordEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					TxRecordEntity x = new TxRecordEntity();
					x.id = re.getInt(1);
					x.sqsj = re.getTimestamp(2);
					x.sjdzje = re.getBigDecimal(3);
					x.chargeStatus = EnumParser.parse(ChargeStatus.class, re.getString(4));
					x.chargeType = EnumParser.parse(ChargeType.class, re.getString(5));
					x.czje = re.getBigDecimal(6);
					x.rate = re.getBigDecimal(7);
					x.cny = re.getBigDecimal(8);
					list.add(x);
				}
				return list == null ? null : list.toArray(new TxRecordEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}


	@Override
	public int addCz(ChargeType type, String name, String zh, BigDecimal czje, BigDecimal usdRate) throws Throwable {
		if (type == null) {
			throw new LogicalException("err_1043");
		}
		if (StringHelper.isEmpty(name)) {
			throw new LogicalException("err_1044");
		}
		if (StringHelper.isEmpty(zh)) {
			throw new LogicalException("err_1045");
		}
		if (czje == null || czje.compareTo(new BigDecimal(0)) <= 0) {
			throw new ParameterException("err_1046");
		}
		ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
		BigDecimal min = new BigDecimal(configureProvider.getProperty(SystemVariable.CHARGE_MIN_AMOUNT));
		if (czje.compareTo(min) < 0) {
			throw new ParameterException("err_1048");
		}
		int accountId = serviceResource.getSession().getAccountId();
		int ddid = insert(getConnection(P2PConst.DB_USER),
				"INSERT INTO T6033 SET F02=?,F03=CURRENT_TIMESTAMP(),F04=?,F05=?,F10=?,F11=?,F13=?,F14=?,F15=?",
				accountId, czje, ChargeStatus.CLZ, zh, type, czje, name, zh);

		double cny =  czje.multiply(usdRate).setScale(2, RoundingMode.HALF_UP).doubleValue();

		String orderTime = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
		
		
		try {
			insert(getConnection(P2PConst.DB_USER),"INSERT INTO T6033_1 SET F01=?,F02=?,F03=?,F04=?",ddid,usdRate ,cny,orderTime);
		} catch (Exception e) {	
			e.printStackTrace();
		}
		return ddid;
	}

	/**
	 * 校验银行卡卡号
	 * 
	 * @param cardId
	 * @return
	 */
	protected boolean checkBankCard(String cardId) {
		if (cardId.trim().length() < 16) {
			return false;
		}
		char bit = getBankCardCheckCode(cardId.substring(0, cardId.length() - 1));
		if (bit == 'N') {
			return false;
		}
		return cardId.charAt(cardId.length() - 1) == bit;
	}
	
	protected char getBankCardCheckCode(String nonCheckCodeCardId) {
		if (nonCheckCodeCardId == null || nonCheckCodeCardId.trim().length() == 0
				|| !nonCheckCodeCardId.matches("\\d+")) {
			// 如果传的不是数据返回N
			return 'N';
		}
		char[] chs = nonCheckCodeCardId.trim().toCharArray();
		int luhmSum = 0;
		for (int i = chs.length - 1, j = 0; i >= 0; i--, j++) {
			int k = chs[i] - '0';
			if (j % 2 == 0) {
				k *= 2;
				k = k / 10 + k % 10;
			}
			luhmSum += k;
		}
		return (luhmSum % 10 == 0) ? '0' : (char) ((10 - luhmSum % 10) + '0');
	}
	
	@Override
	public BigDecimal getB(int bid) throws Throwable {
		if (bid <= 0) {
			bid = getbid();
		}
		return selectBigDecimal(P2PConst.DB_USER, "SELECT F04 FROM T6025 WHERE F02=? AND F03=? ",
				serviceResource.getSession().getAccountId(), bid);
	}
	@Override
	public BigDecimal getBd(int bid) throws Throwable {
		if (bid <= 0) {
			bid = getbid();
		}
		return selectBigDecimal(P2PConst.DB_USER, "SELECT F05 FROM T6025 WHERE F02=? AND F03=? ",
				serviceResource.getSession().getAccountId(), bid);
	}

	@Override
	public String getZwm(int bid) throws Throwable {
		if (bid <= 0) {
			bid = getbid();
		}
		return selectString(P2PConst.DB_USER, "SELECT F04 FROM T6013 WHERE F01=?", bid);
	}

	@Override
	public PagingResult<XlbEntity> searchXlbZr(int bid, Paging paging) throws Throwable {
		ArrayList<Object> parameters = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"SELECT T6027.F07 sj,T6027.F04 zrsl,T6027.F05 sjdz,T6027.F06 zr_zcdz,T6027.F08 zrdz,");
		sql.append("  T6013.F03 ywm,T6013.F04 zwm,T6027.F09 zt");
		sql.append("  FROM T6027 LEFT JOIN T6013 ON T6027.F03=T6013.F01");
		sql.append("  WHERE 1=1");
		sql.append("  AND T6027.F03=? AND T6027.F02=? ");
		parameters.add(bid);
		parameters.add(serviceResource.getSession().getAccountId());
		sql.append(" ORDER BY T6027.F07 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<XlbEntity>() {
			ArrayList<XlbEntity> list = new ArrayList<XlbEntity>();

			@Override
			public XlbEntity[] parse(ResultSet re) throws SQLException {
				while (re.next()) {
					XlbEntity x = new XlbEntity();
					x.sj = re.getTimestamp(1);
					x.zrsl = re.getBigDecimal(2);
					x.sjdz = re.getBigDecimal(3);
					x.zr_zcdz = re.getString(4);
					x.zr_zrdz = re.getString(5);
					x.ywm = re.getString(6);
					x.zwm = re.getString(7);
					x.isPass = EnumParser.parse(IsPass.class, re.getString(8));
					list.add(x);
				}
				return list == null ? null : list.toArray(new XlbEntity[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	private boolean sfsd() throws Throwable {
		String sfsd = selectString(P2PConst.DB_USER, "SELECT F06 FROM T6010 WHERE F01=?",
				serviceResource.getSession().getAccountId());
		if (String.valueOf(IsPass.S).equals(sfsd)) {
			return false;
		}
		return true;
	}

	private boolean gjsmrz() throws SQLException {
		boolean s = false;
		int count = selectInt(P2PConst.DB_USER, "SELECT COUNT(F01) FROM T6011 WHERE F01=? AND F13=?",
				serviceResource.getSession().getAccountId(), IsPass.S);
		if (count > 0) {
			s = true;
		}
		return s;
	}

	public Integer getUserId(String apikey) throws Throwable {
		return selectInt(P2PConst.DB_USER, "select f01 from t6010 where f12 = 'S' and F13 = ?", apikey);
	}

	@Override
	public void cancelOrder(int id, String apikey) throws Throwable {
		int scid = selectInt(P2PConst.DB_USER, "SELECT F02 FROM t6017 WHERE F01=?", id);
		if (scid > 0) {
			cxWt(id, scid, getUserId(apikey));
		}
	}
	

	@Override
	public int addOrder(BigDecimal amount, String payType,String banknumber, BigDecimal usdRate) throws Throwable {
		if (serviceResource.getSession() == null
				|| !serviceResource.getSession().isAuthenticated()) {
			throw new ParameterException("鉴权失败");
		}
		if (amount.compareTo(new BigDecimal(0)) <= 0
				|| StringHelper.isEmpty(payType)) {
			throw new ParameterException("金额或支付类型错误");
		}
		ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
		BigDecimal min = new BigDecimal(configureProvider.getProperty(SystemVariable.CHARGE_MIN_AMOUNT));
		if (amount.compareTo(min) < 0) {
			throw new ParameterException("充值金额小于最低充值金额。");
		}
		int accountId = serviceResource.getSession().getAccountId();
		int ddid = insert(getConnection(P2PConst.DB_USER),
				"INSERT INTO T6033 SET F02=?,F03=CURRENT_TIMESTAMP(),F04=?,F05=?,F11=?,F12=?,F13=?,F06=?",
				accountId, amount, ChargeStatus.WZF,ChargeType.HR,IsPass.S, amount,payType);

		double cny =  amount.multiply(usdRate).setScale(2, RoundingMode.HALF_UP).doubleValue();

		String orderTime = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
		
		
		try {
			insert(getConnection(P2PConst.DB_USER),"INSERT INTO T6033_1 SET F01=?,F02=?,F03=?,F04=?",ddid,usdRate ,cny,orderTime);
		} catch (Exception e) {	
			e.printStackTrace();
		}
		return ddid;
	}

	@Override
	public int getZbDbzg() throws Throwable {
		return selectInt(P2PConst.DB_USER, "SELECT F14 FROM T6010 WHERE F01=?",
				serviceResource.getSession().getAccountId());
	}
	
	@Override
	public BigDecimal getAllZc() throws Throwable {
		BigDecimal allZc = new BigDecimal(0);
		BigDecimal cnyZc = getCny().cny_ze;
		
		allZc = allZc.add(cnyZc);
		UserZhEntity[] userZh = getZhXlb();

		for(UserZhEntity u : userZh){
			allZc = allZc.add(u.xlb_ky.add(u.xlb_dj).multiply(u.zxj));	
		}
		
		return allZc;
	}


	@Override
	public RechargeQrEntity searchRechargeQr() throws Throwable {
		return select(getConnection(P2PConst.DB_USER), new ItemParser<RechargeQrEntity>() {

			@Override
			public RechargeQrEntity parse(ResultSet paramResultSet) throws SQLException {
				RechargeQrEntity entity = new RechargeQrEntity();
				while (paramResultSet.next()) {
					entity.fir = paramResultSet.getString(1);
					entity.sec = paramResultSet.getString(2);
					entity.thir = paramResultSet.getString(3);
					entity.four = paramResultSet.getString(4);
				}
				return entity;
			}
		}, "SELECT F02,F03,F04,F05 FROM T6035 WHERE F01=1");
	}

}